﻿CREATE PROCEDURE [dbo].[categories_get_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @name nvarchar(256) = null
AS
	SELECT
		Categories.CategoryId,
		Categories.Name,
		CountBooks = (SELECT COUNT(1) FROM BooksByCategory WHERE BooksByCategory.CategoryId=Categories.CategoryId)
	FROM Categories
	WHERE
	(@name is NULL OR Categories.Name like '%'+ @name + '%')
	ORDER BY
		CASE WHEN @column = 'Name' AND @direction = 1 THEN Categories.Name END DESC,
		CASE WHEN @column = 'Name' AND @direction = 0 THEN Categories.Name END ASC,
		CASE WHEN @column = 'CountBooks' AND @direction = 1 THEN (SELECT COUNT(1) FROM BooksByCategory WHERE BooksByCategory.CategoryId=Categories.CategoryId) END DESC,
		CASE WHEN @column = 'CountBooks' AND @direction = 0 THEN (SELECT COUNT(1) FROM BooksByCategory WHERE BooksByCategory.CategoryId=Categories.CategoryId) END ASC

	OFFSET ((@page-1)*@size) ROWS
	FETCH NEXT @size ROWS ONLY;

Declare 
@TotalCount int
 SELECT @TotalCount= Count(DISTINCT Categories.CategoryId)
	FROM Categories
	where 
		(@name is NULL OR Categories.Name like '%'+ @name + '%')

RETURN @TotalCount;